//
//  TitleDataBaseManager.m
//  ToYourHeartNews
//
//  Created by XJ－Mac on 16/2/3.
//  Copyright © 2016年 www.lanou3g.com 蓝鸥. All rights reserved.
//

#import "TitleDataBaseManager.h"

@implementation TitleDataBaseManager
//创建数据库
+(TitleDataBaseManager *)shareInstance
{
    //静态指针 创建数据库
    static TitleDataBaseManager *dbManager = nil;
    
    if (!dbManager) {
        dbManager = [[TitleDataBaseManager alloc]init];
    }
    return dbManager;
    
}
///打开数据库
-(void)openDB
{
    //数据库一般放在Document下 后缀:db/sqlite
    //根据人名创建数据库
    NSString *str = [[NSUserDefaults standardUserDefaults]objectForKey:@"name"];
    NSString *pathStr = [str stringByAppendingString:@"titleDataBase.db"];
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:pathStr];
    
    //直接创建数据库
//    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"titleDataBase.db"];
    //语句  创建数据库文件
    //参数1: 数据库路径(转换成UTF8)
    //参数2: 数据库指针地址
    int result = sqlite3_open(path.UTF8String, &dbNewsPoint);
    NSLog(@"打开数据库: %d, 路径: %@", result, path);
}
///关闭数据库
-(void)closeDB
{
    sqlite3_close(dbNewsPoint);
}
///创建表
-(void)createTable
{
    //1.SQL语句
    NSString *createSQL = @"create table if not exists title (expertId text, alias text, picurl text, classification text, concernCount text, name text, headpicurl text, title text, questionCount text, answerCount text, expertDescription text, titleId integer PRIMARY KEY AUTOINCREMENT)";
    //2.执行语句
    //参数1: 数据库指针
    //参数2: sql语句
    //其他参数:NULL
    int result = sqlite3_exec(dbNewsPoint, createSQL.UTF8String, NULL, NULL, NULL);
    //判断
    [self judgeResult:result message:@"创建表"];
}
///删除表
-(void)dropTable
{
    //1.SQL语句
    NSString *dropSQL = @"drop table title";
    //执行
    int result = sqlite3_exec(dbNewsPoint, dropSQL.UTF8String, NULL, NULL, NULL);
    //判断
    [self judgeResult:result message:@"删除表"];
}
///增
-(void)insertTitles:(TitlesModel *)m
{
    //SQL
    NSString *insertSQL = [NSString stringWithFormat:@"insert into title (expertId, alias, picurl, classification, concernCount, name, headpicurl, title, questionCount, answerCount, expertDescription) values('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", m.expertId, m.alias, m.picurl, m.classification, m.concernCount, m.name, m.headpicurl, m.title, m.questionCount, m.answerCount, m.expertDescription];
    //执行
    int result = sqlite3_exec(dbNewsPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    //判断
    [self judgeResult:result message:@"插入"];
}
///删
-(void)deleteTitlesWithId:(NSInteger)titleId
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from title where titleId = '%ld'", titleId];
    int result = sqlite3_exec(dbNewsPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
    [self judgeResult:result message:@"删除"];
}
///改
//-(void)updateTitle:(TitlesModel *)titleM withId:(NSInteger)titleId
//{
//    NSString *updateSQL = [NSString stringWithFormat:@"update title set expertId = '%@', alias = '%@', newsAuther_name = '%@', newsWeburl = '%@', newsType = '%@', newsPk = '%@' where newsId = '%ld'", newM.title, newM.date, newM.auther_name, newM.weburl, newM.type, newM.pk, NewsId];
//    int result = sqlite3_exec(dbNewsPoint, updateSQL.UTF8String, NULL, NULL, NULL);
//    [self judgeResult:result message:@"更新数据"];
//}
///查询
-(NSMutableArray *)select
{
    //查询后 数据处理过程
    //1.获取所有数据
    //2.先遍历行 得到每一行数据
    //3.再遍历当前行 每一列 按照对应关系保存在model中
    //4.model添加到数组 返回
    
    //SQL
    NSString *selectSQL = @"select *from title";
    //创建数据库替身
    sqlite3_stmt *stmt = nil;
    //SQL准备语句
    //把数据库对象/SQL语句/数据库替身 关联在一起
    int result = sqlite3_prepare_v2(dbNewsPoint, selectSQL.UTF8String, -1, &stmt, nil);
    //初始化返回的数组
    NSMutableArray *arr = [NSMutableArray array];
    //判断准备是否成功
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        //遍历每一行数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //逐行获取每一行信息
            const unsigned char *expertId = sqlite3_column_text(stmt, 0);
            const unsigned char *alias = sqlite3_column_text(stmt, 1);
            const unsigned char *picurl = sqlite3_column_text(stmt, 2);
            const unsigned char *classification = sqlite3_column_text(stmt, 3);
            const unsigned char *concernCount = sqlite3_column_text(stmt, 4);
            const unsigned char *name = sqlite3_column_text(stmt, 5);
            const unsigned char *headpicurl = sqlite3_column_text(stmt, 6);
            const unsigned char *title = sqlite3_column_text(stmt, 7);
            const unsigned char *questionCount = sqlite3_column_text(stmt, 8);
            const unsigned char *answerCount = sqlite3_column_text(stmt, 9);
            const unsigned char *expertDescription = sqlite3_column_text(stmt, 10);
            int titleId = sqlite3_column_int(stmt,11);
            //创建model对象
            TitlesModel *m = [[TitlesModel alloc]init];
            m.expertId = [NSString stringWithUTF8String:(const char *)expertId];
            m.alias = [NSString stringWithUTF8String:(const char *)alias];
            m.picurl = [NSString stringWithUTF8String:(const char *)picurl];
            m.classification= [NSString stringWithUTF8String:(const char *)classification];
            NSString *concern = [NSString stringWithUTF8String:(const char *)concernCount];
            NSInteger concernInt = concern.integerValue;
            m.concernCount = @(concernInt);
            m.name = [NSString stringWithUTF8String:(const char *)name];
            m.headpicurl = [NSString stringWithUTF8String:(const char *)headpicurl];
            m.title = [NSString stringWithUTF8String:(const char *)title];
            NSString *question = [NSString stringWithUTF8String:(const char *)questionCount];
            NSInteger questionInt = question.integerValue;
            m.questionCount = @(questionInt);
            NSString *answer = [NSString stringWithUTF8String:(const char *)answerCount];
            NSInteger answerInt = answer.integerValue;
            m.answerCount = @(answerInt);
            m.expertDescription= [NSString stringWithUTF8String:(const char *)expertDescription];
            m.titleId = titleId;
            
            [arr addObject:m];
        }
    }else{
        NSLog(@"失败: %d", result);
    }
    return arr;
}
///判断方法
-(void)judgeResult:(int)r message:(NSString *)mes
{
    if (r == SQLITE_OK) {
        NSLog(@"%@成功", mes);
    }else{
        NSLog(@"%@失败: %d", mes, r);
    }
}

@end
